﻿
Public MustInherit Class PO
    Private poInfo As POInfo ''Được khởi tạo giá trị bằng một POInfo của một bảng bất kỳ ngay khi chạy ựng dụng
    '' Dictionary lưu tất cả các POInfo của tất cả các table
    '' Được khởi tạo lần đầu ngay khi chạy ứng dụng. Key là table name
    Public Shared poInfoAll As New Dictionary(Of String, POInfo)
    Private m_oldValues As Object() ''Giá trị ban đầu
    Private m_newValues As Object() '' Giá trị mới

    Private erro As String = ""
    Private _behave As Integer


    ''' <summary>
    ''' Dùng truy vấn tới database dựa vào các thông số của lớp POInfo để
    ''' load giá trị cho m_oldValues hoặc m_newValues
    ''' Điều kiện trước là poInfo đã được gắn giá trị
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub load(ByVal id As Integer)
        ReDim Me.m_newValues(Me.poInfo.columnCount)
        ReDim Me.m_oldValues(Me.poInfo.columnCount)

        ' Load theo id
        If (id > 0) Then
            ' Map đến database

            Me._behave = CONSTANT.BEHAVE_TYPE._UPDATE

            Dim sql As String
            sql = "SELECT *FROM " & Me.poInfo.TableName & ""
            sql += " WHERE " & Me.poInfo.getKey & " = " & id & ""

            Dim myConnect As MySqlConnect
            myConnect = New MySqlConnect

            Dim da As DataTable
            da = New DataTable
            da = myConnect.executeQuery(sql)
            If (da.Rows.Count = 0) Then
                Return
            End If

            Dim i As Integer
            For i = 0 To Me.poInfo.columnCount - 1
                Me.m_newValues(i) = da.Rows(0)(Me.poInfo.getPOInfoColumn(i).getColumnName)
                Me.m_oldValues(i) = da.Rows(0)(Me.poInfo.getPOInfoColumn(i).getColumnName)
            Next

        Else
            ' tạo mới
            Me._behave = CONSTANT.BEHAVE_TYPE._INSERT

        End If 'End if id >0


    End Sub

    Public MustOverride Function beforeSave(ByVal b As Boolean) As Boolean


    Public MustOverride Function afterSave(ByVal b As Boolean) As Boolean


    Public Function save(ByVal b As Boolean) As Boolean
        If (Not Me.beforeSave(b)) Then
            Me.erro = "ERRO! Before save is not successful."
            MessageBox.Show(Me.erro)
            Return False
        End If

        If (Not Me.afterSave(b)) Then
            Me.erro = "ERRO! After save is not successful."
            MessageBox.Show(Me.erro)
            Return False
        End If

        Select Case Me._behave
            Case CONSTANT.BEHAVE_TYPE._UPDATE
                Me.saveUpdate()
            Case CONSTANT.BEHAVE_TYPE._INSERT
                Me.saveNew()
            Case Else
                Me.erro = "ERRO! Behave of save does not identified"
                MessageBox.Show(erro)
                Return False
        End Select

        Return True
    End Function

    Private Function saveNew() As Boolean
        Dim i As Integer

        For i = 1 To Me.poInfo.columnCount - 1
            If (Me.m_newValues(i) Is Nothing And Me.poInfo.getPOInfoColumn(i).isNullable.Equals(ISNULLABLE.ISNULLABLE_NO)) Then
                Me.erro = "ERRO! Field: " + Me.poInfo.getPOInfoColumn(i).getColumnName + " is mandatory"
                MessageBox.Show(Me.erro)

                Return False
            End If
        Next

        Dim arrLst As ArrayList ' Array list lưu vị trí của các column được set giá trị
        arrLst = New ArrayList

        For i = 0 To Me.poInfo.columnCount - 1
            If (Me.m_newValues(i) IsNot Nothing) Then
                arrLst.Add(i)
            End If
        Next

        Dim sql As String
        i = 0
        sql = "INSERT INTO " & Me.poInfo.TableName & ""
        sql += " ("
        sql += " " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & ""
        For i = 1 To arrLst.Count - 1
            sql += " ," & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & ""
        Next
        sql += " )"
        sql += " VALUES ("
        Select Case Me.poInfo.getPOInfoColumn(arrLst(0)).getDataType
            Case DATA_TYPE.DATA_TYPE_varchar
                sql += " '" & Me.m_newValues(arrLst(0)) & "'"
            Case DATA_TYPE.DATA_TYPE_char
                sql += " '" & Me.m_newValues(arrLst(0)) & "'"
            Case DATA_TYPE.DATA_TYPE_date
                Dim date1 As Date
                date1 = CType(Me.m_newValues(arrLst(0)), Date)
                sql += " '" & date1.ToString("yyyy-MM-dd") & "'"


            Case DATA_TYPE.DATA_TYPE_timestamp
            Case DATA_TYPE.DATA_TYPE_int
                '' Viet: Check Form
                If (Not CheckForm.check(Me.m_newValues(arrLst(0)), DATA_TYPE.DATA_TYPE_int)) Then
                    Me.erro = "Erro: " + Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName + " is " + DATA_TYPE.DATA_TYPE_int
                    MessageBox.Show(Me.erro)
                    Return False
                End If
                sql += " " & Me.m_newValues(arrLst(0)) & ""
            Case DATA_TYPE.DATA_TYPE_text
            Case DATA_TYPE.DATA_TYPE_longtext
            Case Else
                sql += " " & Me.m_newValues(arrLst(0)) & ""
        End Select


        For i = 1 To arrLst.Count - 1
            Select Case Me.poInfo.getPOInfoColumn(arrLst(i)).getDataType
                Case DATA_TYPE.DATA_TYPE_varchar
                    sql += " ,'" & Me.m_newValues(arrLst(i)) & "'"
                Case DATA_TYPE.DATA_TYPE_char
                    sql += " ,'" & Me.m_newValues(arrLst(i)) & "'"
                Case DATA_TYPE.DATA_TYPE_date
                    Dim date1 As Date
                    date1 = Date.Parse(Me.m_newValues(arrLst(i)).ToString)
                    sql += " ,'" & date1.ToString("yyyy-MM-dd") & "'"

                Case DATA_TYPE.DATA_TYPE_timestamp
                Case DATA_TYPE.DATA_TYPE_int
                    '' Viet: Check Form
                    If (Not CheckForm.check(Me.m_newValues(arrLst(i)), DATA_TYPE.DATA_TYPE_int)) Then
                        Me.erro = "Erro: " + Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName + " is " + DATA_TYPE.DATA_TYPE_int
                        MessageBox.Show(Me.erro)
                        Return False
                    End If
                    sql += " ," & Me.m_newValues(arrLst(i)) & ""
                Case DATA_TYPE.DATA_TYPE_text
                Case DATA_TYPE.DATA_TYPE_longtext
                Case Else
                    sql += " ," & Me.m_newValues(arrLst(i)) & ""
            End Select

        Next
        sql += " )"

        Dim myConnect As MySqlConnect
        myConnect = New MySqlConnect
        myConnect.executeUpdate(sql)

        Dim da As DataTable = New DataTable

        Dim sqlWhere As String = "" & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = "

        Select Case Me.poInfo.getPOInfoColumn(arrLst(0)).getDataType
            Case DATA_TYPE.DATA_TYPE_varchar
                sqlWhere += " '" & Me.getValue(Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName) & "' "
            Case DATA_TYPE.DATA_TYPE_char
                sqlWhere += " '" & Me.getValue(Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName) & "' "
            Case DATA_TYPE.DATA_TYPE_date
                Dim date1 As Date
                date1 = CType(Me.getValue(Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName), Date)
                sqlWhere += " '" & date1.ToString("yyyy-MM-dd") & "'"


            Case DATA_TYPE.DATA_TYPE_timestamp
            Case DATA_TYPE.DATA_TYPE_text
            Case DATA_TYPE.DATA_TYPE_longtext
            Case Else
                sql += " " & Me.m_newValues(arrLst(0)) & ""
        End Select
        da = New Query(Me, sqlWhere).dataTable
        If (da Is Nothing) Then
            Return False
        End If
        Me.setValue(Me.poInfo.getPOInfoColumn(0).getColumnName, da.Rows(0)(Me.poInfo.getPOInfoColumn(0).getColumnName))
        Return True
    End Function

    Private Function saveUpdate() As Boolean

        If (Me.m_oldValues(0) Is Nothing) Then
            Me.erro = "ERRO! Me is Nothing. Does not update into database"
            MessageBox.Show(Me.erro)
            Return False
        End If

        Dim i As Integer = 0

        Dim arrLst As ArrayList ' Array list lưu vị trí của các column được thay đổi giá trị
        arrLst = New ArrayList

        For i = 0 To Me.poInfo.columnCount - 1
            If (Not Me.m_oldValues(i) Is Nothing And Not Me.m_newValues(i) Is Nothing) Then
                If (Not Me.m_newValues(i).Equals(Me.m_oldValues(i))) Then
                    arrLst.Add(i)
                End If
            End If
            
        Next

        If (arrLst.Count = 0) Then
            Me.erro = "Data is not change! Do not need update into database"
            MessageBox.Show(Me.erro)
            Return False
        End If


        Dim sql As String
        sql = "UPDATE " & Me.poInfo.TableName & ""
        Select Case Me.poInfo.getPOInfoColumn(arrLst(0)).getDataType
            Case DATA_TYPE.DATA_TYPE_varchar
                sql += " SET " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = '" & Me.m_newValues(arrLst(0)) & "' "
            Case DATA_TYPE.DATA_TYPE_char
                sql += " SET " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = '" & Me.m_newValues(arrLst(0)) & "' "
            Case DATA_TYPE.DATA_TYPE_date
                Dim date1 As Date
                date1 = Date.Parse(Me.m_newValues(arrLst(0)).ToString)
                sql += " SET " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = '" & date1.ToString("yyyy-MM-dd") & "'"
            Case DATA_TYPE.DATA_TYPE_timestamp
            Case DATA_TYPE.DATA_TYPE_int
                '' Viet: Check Form
                If (Not CheckForm.check(Me.m_newValues(arrLst(0)), DATA_TYPE.DATA_TYPE_int)) Then
                    Me.erro = "Erro: " + Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName + " is " + DATA_TYPE.DATA_TYPE_int
                    MessageBox.Show(Me.erro)
                    Return False
                End If
                sql += " SET " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = " & Me.m_newValues(arrLst(0)) & " "
            Case DATA_TYPE.DATA_TYPE_text
            Case DATA_TYPE.DATA_TYPE_longtext
            Case Else
                sql += " SET " & Me.poInfo.getPOInfoColumn(arrLst(0)).getColumnName & " = " & Me.m_newValues(arrLst(0)) & " "
        End Select

        For i = 1 To arrLst.Count - 1
            Select Case Me.poInfo.getPOInfoColumn(arrLst(i)).getDataType
                Case DATA_TYPE.DATA_TYPE_varchar
                    sql += " , " & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & " = '" & Me.m_newValues(arrLst(i)) & "'"
                Case DATA_TYPE.DATA_TYPE_char
                    sql += " , " & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & " = '" & Me.m_newValues(arrLst(i)) & "'"
                Case DATA_TYPE.DATA_TYPE_date
                    Dim date1 As Date
                    date1 = Date.Parse(Me.m_newValues(arrLst(i)).ToString)
                    sql += " , " & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & " = '" & date1.ToString("yyyy-MM-dd") & "'"

                Case DATA_TYPE.DATA_TYPE_timestamp
                Case DATA_TYPE.DATA_TYPE_int
                    '' Viet: Check Form
                    If (Not CheckForm.check(Me.m_newValues(arrLst(i)), DATA_TYPE.DATA_TYPE_int)) Then
                        Me.erro = "Erro: " + Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName + " is " + DATA_TYPE.DATA_TYPE_int
                        MessageBox.Show(Me.erro)

                        Return False
                    End If
                    sql += " , " & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & " = " & Me.m_newValues(arrLst(i)) & ""
                Case DATA_TYPE.DATA_TYPE_text
                Case DATA_TYPE.DATA_TYPE_longtext
                Case Else
                    sql += " , " & Me.poInfo.getPOInfoColumn(arrLst(i)).getColumnName & " = " & Me.m_newValues(arrLst(i)) & ""
            End Select


        Next
        sql += " WHERE " & Me.poInfo.getKey & " = " & Me.getOldValue(Me.poInfo.getKey) & ""

        Dim myConnect As MySqlConnect
        myConnect = New MySqlConnect

        Return myConnect.executeUpdate(sql)
    End Function


    Public Function delete() As Boolean

        If (Not beforeDelete()) Then
            Me.erro = "ERRO! Before delete is not successful."
            MessageBox.Show(Me.erro)
            Return False
        End If

        If (Not afterDelete()) Then
            Me.erro = "ERRO! After delete is not successful."
            MessageBox.Show(Me.erro)
            Return False
        End If

        Dim sql As String
        sql = "DELETE FROM " & Me.getPoInfo.TableName & ""
        sql += " WHERE " & Me.getPoInfo.getKey & " = " & Me.getOldValue(Me.getPoInfo.getKey) & ""

        Dim myConnect As MySqlConnect
        myConnect = New MySqlConnect

        Return myConnect.executeUpdate(sql)

    End Function

    Public MustOverride Function beforeDelete() As Boolean


    Public MustOverride Function afterDelete() As Boolean

    Public Function setPOinfo(ByVal poInfo As POInfo) As Boolean
        Me.poInfo = poInfo
    End Function


    Public Function setValue(ByVal col As Integer, ByVal value As Object) As Boolean
        If (col > Me.poInfo.columnCount - 1) Then
            Me.erro = "Col is not correct!"
            MessageBox.Show(Me.erro)
            Return False
        End If
        If (Me.m_oldValues(col) Is Nothing) Then
            Me.m_oldValues(col) = value
        End If
        Me.m_newValues(col) = value
        Return True
    End Function

    Public Function setValue(ByVal columnName As String, ByVal value As Object) As Boolean
        Dim i As Integer
        i = 0
        For i = 0 To Me.poInfo.columnCount - 1
            If (Me.poInfo.getPOInfoColumn(i).getColumnName.Equals(columnName)) Then
                Me.setValue(i, value)
                Return True
            End If
        Next
        Me.erro = "ColumnName is does not exits!"
        MessageBox.Show(Me.erro)


        Return False

    End Function

    Private Function getOldValue(ByVal col As Integer) As Object

        If (Me Is Nothing) Then
            Return Nothing
        End If
        If (col > Me.poInfo.columnCount - 1) Then
            Me.erro = "Col is not correct!"
            MessageBox.Show(Me.erro)

            Return False
        End If
        Return Me.m_oldValues(col)
    End Function

    Private Function getOldValue(ByVal columnName As String) As Object

        If (Me Is Nothing) Then
            Return Nothing
        End If

        Dim i As Integer
        i = 0
        For i = 0 To Me.poInfo.columnCount - 1
            If (Me.poInfo.getPOInfoColumn(i).getColumnName = columnName) Then
                Return Me.getOldValue(i)
            End If
        Next
        Me.erro = "ColumnName is does not exits!"
        MessageBox.Show(Me.erro)

        Return Nothing
    End Function



    Public Function getValue(ByVal col As Integer) As Object
        If (Me Is Nothing) Then
            Return Nothing
        End If

        If (col > Me.poInfo.columnCount - 1) Then
            Me.erro = "Col is not correct!"
            MessageBox.Show(Me.erro)

            Return False
        End If

        If (Me.m_newValues(col) Is Nothing) Then
            Return Me.m_oldValues(col)
        End If
        Return Me.m_newValues(col)
    End Function

    Public Function getValue(ByVal columnName As String) As Object
        If (Me Is Nothing) Then
            Return Nothing
        End If

        Dim i As Integer
        i = 0
        For i = 0 To Me.poInfo.columnCount - 1
            If (Me.poInfo.getPOInfoColumn(i).getColumnName = columnName) Then
                Return Me.getValue(i)
            End If
        Next
        Me.erro = "ColumnName is does not exits!"
        MessageBox.Show(Me.erro)

        Return Nothing
    End Function


    Public Function getPoInfo() As POInfo
        Return Me.poInfo
    End Function

    Public Function showNewInfo() As Boolean
        Dim i As Integer
        i = 0
        Dim info As String = "Thông tin chi tiết new_row: "

        For i = 0 To Me.poInfo.columnCount - 1
            info += "" & Me.getValue(i) & ", "
        Next

        MessageBox.Show(info)

        Return True
    End Function

    Public Function showOldInfo() As Boolean
        Dim i As Integer
        i = 0
        Dim info As String = "Thông tin chi tiết  old_row: "

        For i = 0 To Me.poInfo.columnCount - 1
            info += "" & Me.getOldValue(i) & ", "
        Next

        MessageBox.Show(info)

        Return True
    End Function

    Public Function setBehave(ByVal behave As Integer) As Boolean
        Me._behave = behave
    End Function

    Public Function getBehave() As Integer
        Return Me._behave
    End Function

    Public Shared Function iniApp() As Boolean
        Config.loadConfig()

        Dim sql As String
        sql = "SELECT TABLE_NAME FROM information_schema.TABLES"
        sql += " WHERE TABLE_SCHEMA = '" & Config.TABLE_SCHEMA & "'"

        Dim myConnect As MySqlConnect
        myConnect = New MySqlConnect

        Dim da As DataTable
        da = myConnect.executeQuery(sql)
        If (da Is Nothing) Then
            MessageBox.Show("Cau truy van bi sai")
        End If

        Dim i As Integer = 0
        For i = 0 To da.Rows.Count - 1
            Dim table_name As String = da.Rows(i)("TABLE_NAME")
            Dim poInfo As POInfo = New POInfo(table_name, True)
            PO.poInfoAll.Add(table_name, poInfo)
        Next
    End Function

    Public Function dataTypeParseAble(ByVal typeFrom, ByVal typeTo) As Boolean

        Return True
    End Function
End Class

